********************************************************************************
** 	TITLE: b12_clean_mt_morris_educ.do
**
**	PROJECT: IGNITE
** 
**  PURPOSE: Cleaning Mt. Morris Schools Education Data to study inmate characteristics
**	and to bring in definitions for different education performance measures.
** 
*******************************************************************************/
				set sortseed 13

		set scheme burd4
		graph set window fontface "Times New Roman" //makes times new roman the default
import excel "$input_data/mt_morris_02jan2024/IGNITE Harvard Data december 2023.xlsx", firstrow clear
ren (A LastName) (first last)
ren *, lower()

ren (studentid completedged hashsdiplomayn) ///
	(student_id completed_ged has_hs_diploma)

ren (readingpreassessmentscore readingpreassessmentdate ///
	readingpostassessmentscore readingpostassessmentdate) ///
	(reading_pre reading_pre_date reading_post reading_post_date)
	
ren (mathpreassessmentscore mathpreassessmentdate ///
	mathpostassessmentscore mathpostassessmentdate) ///
	(math_pre math_pre_date math_post  math_post_date)
	
ren (gedsubject gedscore gedtestdate) ///
	(ged_subject1 ged_score1 ged_testdate1)
	
ren (u v w x y z aa ab ac) ///
	(ged_subject2 ged_score2 ged_testdate2 ///
	 ged_subject3 ged_score3 ged_testdate3 ///
	 ged_subject4 ged_score4 ged_testdate4)

duplicates tag fullname dob, gen(tag)
drop if tag & mi(student_id )
drop tag
	
//Saving cleaned file to be used for balance test	
save "$output_data/mt_morris_eudc.dta", replace



/*
Bringing in Academic Test Benchmarks
*/

label define m_val 1 "Beginning Literacy" 2 "Beginning Basic" 3 "Low Intermediate" 4 "Middle Intermediate" 5 "High Intermediate" 6 "Adult Secondary"

label define r_val 1 "Beginning Literacy" 2 "Beginning Basic" 3 "Low Intermediate" 4 "High Intermediate" 5 "Low Adult Secondary" 6 "High Adult Secondary"


/*
Bringing in Test Grade equivalences
https://www.casas.org/docs/default-source/product-brochures/reading-goals-grade-level-equivalents.pdf?sfvrsn=56393d5a_6?Status=Master
https://www.casas.org/docs/default-source/product-brochures/math-goals-grade-level-equivalents.pdf?sfvrsn=2b2d3d5a_4?Status=Master
*/
label define m_grval 0 "Grade k" 1 "Grade 1" 2 "Grade 2" 3 "Grade 3" 4 "Grade 4" 5 "Grade 5" 6 "Grade 6" 7 "Grade 7" 8 "Grade 8" 9 "Grade 9" 10 "Grade 10" 11 "Grade 11" 12 "Grade 12" 

label define r_grval 0 "Grade k" 1 "Grade 1" 2 "Grade 2" 3 "Grade 3" 4 "Grade 4" 5 "Grade 5" 6 "Grade 6" 7 "Grade 7" 8 "Grade 8" 9 "Grade 9" 10 "Grade 10" 11 "Grade 11" 12 "Grade 12" 


		destring reading_pre reading_post math_pre math_post, replace force
		foreach v in reading_pre reading_post math_pre math_post{
			replace `v' = . if `v'<50
		}

*lvls		
foreach t in pre post{
gen math_lvl_`t' = .

replace math_lvl_`t' = 1 if math_`t' <= 193

replace math_lvl_`t' = 2 if math_`t' >= 194 & math_`t' <= 203

replace math_lvl_`t' = 3 if math_`t' >= 204 & math_`t' <= 214

replace math_lvl_`t' = 4 if math_`t' >= 215 & math_`t' <= 225

replace math_lvl_`t' = 5 if math_`t' >= 226 & math_`t' <= 235

replace math_lvl_`t' = 6 if math_`t' >= 236 & math_`t' != .

la values math_lvl_`t' m_val
}

foreach t in pre post{
gen reading_lvl_`t' = .

replace reading_lvl_`t' = 1 if reading_`t' <= 203

replace reading_lvl_`t' = 2 if reading_`t' >= 204 & reading_`t' <= 216

replace reading_lvl_`t' = 3 if reading_`t' >= 217 & reading_`t' <= 227

replace reading_lvl_`t' = 4 if reading_`t' >= 228 & reading_`t' <= 238

replace reading_lvl_`t' = 5 if reading_`t' >= 239 & reading_`t' <= 248

replace reading_lvl_`t' = 6 if reading_`t' >= 249 & reading_`t' != .

la values reading_lvl_`t' r_val
}

gen delta_reading = reading_post - reading_pre 
gen delta_math = math_post - math_pre

gen delta_lvl_reading = reading_lvl_post - reading_lvl_pre 
gen delta_lvl_math = math_lvl_post - math_lvl_pre 
		
		qui su reading_pre if !mi(delta_reading), de
		gen std_delta_reading = ((reading_post-r(mean))-(reading_pre-r(mean)))/r(sd)
		
		qui su math_pre if !mi(delta_math), de
		gen std_delta_math = ((math_post-r(mean))-(math_pre-r(mean)))/r(sd)
		
		qui su reading_lvl_pre if !mi(delta_lvl_reading), de
		gen std_delta_reading_lvl = ((reading_lvl_post-r(mean))-(reading_lvl_pre-r(mean)))/r(sd)
		
		qui su math_lvl_pre if !mi(delta_lvl_math), de
		gen std_delta_math_lvl = ((math_lvl_post-r(mean))-(math_lvl_pre-r(mean)))/r(sd)

		
foreach s in math reading{
foreach t in pre post{
*Create date test taken variable
gen `s'_`t'_t = date(`s'_`t'_date, "DMY") 
format `s'_`t'_t %td
gen `s'_`t'_month = mofd(`s'_`t'_t)
}
}	
foreach s in math reading{
*POST IGNITE BASED ON Post Exam; September 2020 = 728
gen `s'_ignite = . 
replace `s'_ignite = 1 if  (`s'_post_month >=  mofd(date("9/08/2020","MDY")) & !missing(`s'_post_month)) == 1
replace `s'_ignite = 0 if  (`s'_post_month < mofd(date("9/08/2020","MDY")) & !missing(`s'_post_month)) == 1
}


****************************Creating Grade Equivalences
foreach t in pre post{
gen math_gr_`t' = .
//https://www.casas.org/docs/default-source/product-brochures/math-goals-grade-level-equivalents.pdf?sfvrsn=2b2d3d5a_4?Status=Master
//184 and below
replace math_gr_`t' = 0 if math_`t' <= 184
//185 – 193 
replace math_gr_`t' = 1 if math_`t' >= 185 & math_`t' <= 193
//194 – 198 
replace math_gr_`t' = 2 if math_`t' >= 194 & math_`t' <= 198
//199 – 203
replace math_gr_`t' = 3 if math_`t' >= 199 & math_`t' <= 203
//204 – 209
replace math_gr_`t' = 4 if math_`t' >= 204 & math_`t' <= 209
//210 – 214
replace math_gr_`t' = 5 if math_`t' >= 210 & math_`t' <= 214
//215 – 221
replace math_gr_`t' = 6 if math_`t' >= 215 & math_`t' <= 221
//222 – 225 //226 – 228
replace math_gr_`t' = 7 if math_`t' >= 222 & math_`t' <= 228
//229 – 235
replace math_gr_`t' = 8 if math_`t' >= 229 & math_`t' <= 235
//236 – 240
replace math_gr_`t' = 9 if math_`t' >= 236 & math_`t' <= 240
//241 – 244
replace math_gr_`t' = 10 if math_`t' >= 241 & math_`t' <= 244
//245 – 248
replace math_gr_`t' = 11 if math_`t' >= 245 & math_`t' <= 248
//249 and above
replace math_gr_`t' = 12 if math_`t' >= 249 & math_`t' != .

la values math_gr_`t' m_grval
}

foreach t in pre post{
//https://www.casas.org/docs/default-source/product-brochures/reading-goals-grade-level-equivalents.pdf?sfvrsn=56393d5a_6?Status=Master
gen reading_gr_`t' = .
//193 and below 
replace reading_gr_`t' = 0 if reading_`t' <= 193
//194 – 203
replace reading_gr_`t' = 1 if reading_`t' >= 194 & reading_`t' <= 203
//204 – 210
replace reading_gr_`t' = 2 if reading_`t' >= 204 & reading_`t' <= 210
//211 – 216
replace reading_gr_`t' = 3 if reading_`t' >= 211 & reading_`t' <= 216
//217 – 222
replace reading_gr_`t' = 4 if reading_`t' >= 217 & reading_`t' <= 222
//223 – 227
replace reading_gr_`t' = 5 if reading_`t' >= 223 & reading_`t' <= 227
//228 – 230 
replace reading_gr_`t' = 6 if reading_`t' >= 228 & reading_`t' <= 230
//231 – 234
replace reading_gr_`t' = 7 if reading_`t' >= 231 & reading_`t' <= 234
//235 – 238
replace reading_gr_`t' = 8 if reading_`t' >= 235 & reading_`t' <= 238
//239 – 243 
replace reading_gr_`t' = 9 if reading_`t' >= 239 & reading_`t' <= 243
//244 – 248
replace reading_gr_`t' = 10 if reading_`t' >= 244 & reading_`t' <= 248
//249 – 253
replace reading_gr_`t' = 11 if reading_`t' >= 249 & reading_`t' <= 253
//254 and above 
replace reading_gr_`t' = 12 if reading_`t' >= 254 & reading_`t' != .

la values reading_gr_`t' r_grval
}

//Saving dataset to be used for pre-post grade distributions
save "$output_data/mt_morris_eudc_score_lvl.dta", replace

************************************************************
* Mt. Morris Chromebook Logins 
***********************************************************

import excel "$input_data/mtmorris_22may2023/LoginLogoutTimes_05-22-2023_unencrypted.xlsx", ///
	clear firstrow cellrange(A2) allstring
set scheme burd4
drop if UserType!="Student"
ren (LastName FirstName) (last first)
replace last=proper(last)

replace first=proper(first)

gen floor = 999
foreach f in 3 4 5{
	replace floor = `f' if strpos(CampusID,"`f'")
}
replace floor = 999 if strpos(CampusID,"GCJ 3rd floor | GCJ 4th floor |") 
lab define flab 999 "Unspecified" 3 "3rd Floor" 4 "4th Floor" 5 "5th Floor"
lab values floor flab

gen date=date(Date,"MDY")
format date %td

gen login_time = clock(LoginTime, "hm")
format login_time %tchh:MM_AM
drop if floor==999

//Saving dataset to be used for Chromebook Logins
save "$output_data/mt_morris_chromebooks_login.dta", replace
